
/*

exec sp_search_open_pos '%',-1,'','','2005',0

*/

ALTER       PROC sp_search_open_pos @poNumber VARCHAR(50), @vendorId INT, @validForm DATETIME, @validTo DATETIME, @year VARCHAR(10), @showExpired BIT
AS
BEGIN
	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)
	DECLARE @PurchaseOrder_ID INT
	DECLARE @PurchaseOrder_Number VARCHAR(50)
	DECLARE @PurchaseOrder_Date VARCHAR(50)
	DECLARE @Vendor_ID INT
	DECLARE @Vendor_Name VARCHAR(50)
	DECLARE @Valid_From VARCHAR(50)
	DECLARE @Valid_To VARCHAR(50)
	DECLARE @Max_Value Float
	DECLARE @Remain_Value NUMERIC
	DECLARE @Issued_Value Float

	DECLARE @results TABLE ( PurchaseOrder_Id INT, PONumber VARCHAR(50), Vendor_Name VARCHAR(50), vendor_id INT, PODate VARCHAR(50), Valid_From VARCHAR(50),
	Valid_To VARCHAR(50), MAX_Value Float, Remaining_Value Float )

	-- Change the Status Of all the Expired Open POS to Closed
	Update PurchaseOrder_Table SET PO_Status = 'Closed' WHERE PurchaseOrder_Type = 1 AND PO_Valid_To < DATEADD ( d , -1, GETDATE() ) 

	SET @SQLString = N' DECLARE PurchaseOrderCursor CURSOR FOR SELECT PurchaseOrder_Id, PurchaseOrder_No, CONVERT( VARCHAR, PO_Date, 101 ), Vendor_Id, CONVERT( VARCHAR, PO_Valid_From, 101), CONVERT( VARCHAR, PO_Valid_To, 101), PO_Max_Value FROM PurchaseOrder_Table WHERE PurchaseOrder_Type = 1 AND PO_Status <> ''Closed'' AND PO_Status <> ''New'' AND PO_Status <> ''Cancelled'''
	SET @ParamDefinition = N'@Prm_PONumber VARCHAR(50), @Prm_Vendor_ID INT, @Prm_Valid_From DATETIME, @Prm_Valid_To DATETIME, @Prm_Year VARCHAR(10), @Prm_Show_Expired BIT'

	IF ( @poNumber IS NOT NULL AND LTRIM(RTRIM( @poNumber ) ) <> '' )
	BEGIN
		SET @SQLString = @SQLString + ' AND PurchaseOrder_No like @Prm_PONumber'
	END

	IF ( @vendorId IS NOT NULL AND @vendorId <> -1 )
	BEGIN
		SET @SQLString = @SQLString + ' AND Vendor_Id = @Prm_Vendor_ID'
	END

	IF ( @validForm IS NOT NULL AND LTRIM(RTRIM( @validForm ) ) <> '' AND @validForm > 'Jan  1 1900 12:00AM' )
	BEGIN
		SET @SQLString = @SQLString + ' AND PO_Valid_From >= @Prm_Valid_From'
	END

	IF ( @validTo IS NOT NULL AND LTRIM(RTRIM( @validTo ) ) <> '' AND  @validTo > 'Jan  1 1900 12:00AM'  )
	BEGIN
		SET @SQLString = @SQLString + ' AND PO_Valid_To <= @Prm_Valid_To'
	END

	IF ( @year IS NOT NULL AND LTRIM(RTRIM( @year ) ) <> '' )
	BEGIN
		SET @SQLString = @SQLString + ' AND YEAR( PO_Date ) = @Prm_Year'
	END

	SET @SQLString = @SQLString + ' ORDER BY PO_Date, PurchaseOrder_No'

	EXECUTE sp_executesql @SQLString, @ParamDefinition, @Prm_PONumber = @poNumber, @Prm_Vendor_ID = @vendorId, @Prm_Valid_From = @validForm, @Prm_Valid_To = @validTo, @Prm_Year = @year, @Prm_Show_Expired = @showExpired
	
	OPEN PurchaseOrderCursor
	FETCH NEXT FROM PurchaseOrderCursor INTO @PurchaseOrder_ID, @PurchaseOrder_Number, @PurchaseOrder_Date, @Vendor_ID, @Valid_From, @Valid_To, @Max_Value
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Vendor_Name = V_Name FROM VENDOR WHERE Vendor_Id = @Vendor_ID
		SET @Issued_Value = 0
		SELECT @Issued_Value = ISNULL( SUM( PO_Total ), 0 ) FROM PurchaseOrder_Table WHERE OpenPO_Id = @PurchaseOrder_ID
		
		INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, vendor_id, PODate, Valid_From, Valid_To, MAX_Value, Remaining_Value )
		VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @vendor_id, @PurchaseOrder_Date, @Valid_From, @Valid_To, @Max_Value, @Max_Value - @Issued_Value )

		FETCH NEXT FROM PurchaseOrderCursor INTO @PurchaseOrder_ID, @PurchaseOrder_Number, @PurchaseOrder_Date, @Vendor_ID, @Valid_From, @Valid_To, @Max_Value
	END

  	close PurchaseOrderCursor
  	deallocate PurchaseOrderCursor

	SELECT * FROM @results WHERE Valid_From <= DATEADD ( d , 0, GETDATE() )
END


